"""
创建数据清洗相关的数据库表
该脚本用于初始化或更新数据清洗模块所需的数据库表
"""
import sys
import os
from pathlib import Path

# 添加项目根目录到Python路径
current_dir = os.path.dirname(os.path.abspath(__file__))
project_root = str(Path(current_dir).parent.parent)
sys.path.insert(0, project_root)

from sqlalchemy.orm import Session
from backend.database.database import engine, Base
from backend.data_clean.models import (
    DataCleanTask, BrandConfig, CommonProductName, 
    UnitConfig, MarketingWordConfig, SystemConfig,
    UnitConversion
)
import logging
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Text, Boolean, DateTime, ForeignKey
from sqlalchemy.inspect import inspect
from datetime import datetime

# 设置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def create_tables():
    """创建数据清洗相关的数据库表"""
    try:
        # 创建所有表
        logger.info("开始创建数据库表...")
        
        # 显式创建单位转换表
        logger.info("尝试显式创建UnitConversion表...")
        # 检查表是否已存在
        inspector = inspect(engine)
        if 'unit_conversion' not in inspector.get_table_names():
            metadata = MetaData()
            unit_conversion = Table(
                'unit_conversion', metadata,
                Column('id', Integer, primary_key=True),
                Column('source_unit', String(50), nullable=False),
                Column('target_unit', String(50), nullable=False),
                Column('conversion_formula', String(255), nullable=False),
                Column('description', Text, nullable=True),
                Column('is_active', Boolean, default=True),
                Column('created_at', DateTime, default=datetime.now),
                Column('updated_at', DateTime, default=datetime.now, onupdate=datetime.now)
            )
            metadata.create_all(engine)
            logger.info("手动创建UnitConversion表成功")
        else:
            logger.info("UnitConversion表已存在")
        
        # 创建其他表
        Base.metadata.create_all(bind=engine, tables=[
            DataCleanTask.__table__,
            BrandConfig.__table__,
            CommonProductName.__table__,
            UnitConfig.__table__,
            MarketingWordConfig.__table__,
            SystemConfig.__table__,
            UnitConversion.__table__
        ])
        
        # 检查表是否创建成功
        inspector = inspect(engine)
        table_names = inspector.get_table_names()
        logger.info(f"数据库中的表: {table_names}")
        
        for table_name in ['data_clean_tasks', 'brand_config', 'common_product_names', 
                           'unit_config', 'marketing_word_config', 'system_config', 'unit_conversion']:
            if table_name in table_names:
                logger.info(f"表 {table_name} 已成功创建")
            else:
                logger.warning(f"表 {table_name} 未创建！")
        
        logger.info("成功创建数据清洗相关表")
        return True
    except Exception as e:
        logger.error(f"创建数据清洗相关表失败: {str(e)}")
        import traceback
        logger.error(traceback.format_exc())
        return False

def insert_default_data(db: Session):
    """插入默认数据"""
    try:
        # 添加默认的系统配置
        system_configs = [
            {"key": "SPECIFICATION_PATTERN", "value": r'\d+\.?\d*\s*(?:{units})', "description": "规格匹配模式"},
            {"key": "BRAND_CHAR_RANGE", "value": r'[\u4e00-\u9fa5]', "description": "品牌字符范围"},
            {"key": "BRAND_MAX_LENGTH", "value": "3", "description": "品牌最大长度"},
            {"key": "CLEAN_CHARS_PATTERN", "value": r'[\s/\\-]+', "description": "清理字符模式"}
        ]
        
        for config in system_configs:
            existing = db.query(SystemConfig).filter(SystemConfig.key == config["key"]).first()
            if not existing:
                db.add(SystemConfig(**config))
                logger.info(f"添加系统配置: {config['key']}")
        
        # 添加默认的单位配置
        default_units = [
            {"name": "个", "type": "计数"},
            {"name": "件", "type": "计数"},
            {"name": "包", "type": "计数"},
            {"name": "袋", "type": "计数"},
            {"name": "箱", "type": "计数"},
            {"name": "瓶", "type": "计数"},
            {"name": "罐", "type": "计数"},
            {"name": "盒", "type": "计数"},
            {"name": "片", "type": "计数"},
            {"name": "g", "type": "重量"},
            {"name": "克", "type": "重量"},
            {"name": "kg", "type": "重量"},
            {"name": "千克", "type": "重量"},
            {"name": "公斤", "type": "重量"},
            {"name": "斤", "type": "重量"},
            {"name": "ml", "type": "体积"},
            {"name": "毫升", "type": "体积"},
            {"name": "l", "type": "体积"},
            {"name": "L", "type": "体积"},
            {"name": "升", "type": "体积"}
        ]
        
        for unit in default_units:
            existing = db.query(UnitConfig).filter(UnitConfig.name == unit["name"]).first()
            if not existing:
                db.add(UnitConfig(**unit))
                logger.info(f"添加单位配置: {unit['name']}")
        
        # 添加默认的单位转换规则
        default_conversions = [
            {"source_unit": "g", "target_unit": "斤", "conversion_formula": "x / 500", "description": "克转斤"},
            {"source_unit": "克", "target_unit": "斤", "conversion_formula": "x / 500", "description": "克转斤"},
            {"source_unit": "kg", "target_unit": "斤", "conversion_formula": "x * 2", "description": "千克转斤"},
            {"source_unit": "千克", "target_unit": "斤", "conversion_formula": "x * 2", "description": "千克转斤"},
            {"source_unit": "公斤", "target_unit": "斤", "conversion_formula": "x * 2", "description": "公斤转斤"},
            {"source_unit": "ml", "target_unit": "升", "conversion_formula": "x / 1000", "description": "毫升转升"},
            {"source_unit": "毫升", "target_unit": "升", "conversion_formula": "x / 1000", "description": "毫升转升"},
            {"source_unit": "l", "target_unit": "升", "conversion_formula": "x", "description": "升转升"},
            {"source_unit": "L", "target_unit": "升", "conversion_formula": "x", "description": "升转升"}
        ]
        
        for conv in default_conversions:
            existing = db.query(UnitConversion).filter(
                UnitConversion.source_unit == conv["source_unit"],
                UnitConversion.target_unit == conv["target_unit"]
            ).first()
            if not existing:
                db.add(UnitConversion(**conv))
                logger.info(f"添加单位转换规则: {conv['source_unit']} -> {conv['target_unit']}")
        
        db.commit()
        logger.info("成功插入默认数据")
        return True
    except Exception as e:
        db.rollback()
        logger.error(f"插入默认数据失败: {str(e)}")
        return False

if __name__ == "__main__":
    from backend.database.database import get_db
    db = next(get_db())
    create_tables()
    insert_default_data(db) 